# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json

import os

import time
import xlrd
import xlsxwriter
import xlwt
from xlutils.copy import copy
start = time.time()
from xlwt import XFStyle


class TencentapplicationPipeline(object):
    def process_item(self, item, spider):
        if item:
            global start
            end_time = time.time()
            print('共花费%s秒'%(end_time-start))
            # print(dict(item))
            # print(type(num))
            book_name_xls = '腾讯应用宝工作簿.xls'
            sheet_name_xls = '软件表'

            self.write_excel_xls(book_name_xls, sheet_name_xls, dict(item))
         # return item

    def write_excel_xls(self,path, sheet_name, value):
        if os.path.exists(path):
            self.write_excel_xls_append(path,value)
        else:
            index = len(value)  # 获取需要写入数据的行数
            workbook = xlwt.Workbook()  # 新建一个工作簿
            row = 1
            col = 0

            sheet = workbook.add_sheet(sheet_name)  # 在工作簿中新建一个表格
            #标题加粗
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.name = '黑体'  # 指定“宋体”
            font.bold = True
            style.font = font

            # 用符号标记位置，例如：A列1行
            sheet.write(0, 0, '分类')
            sheet.write(0, 1, '应用名')
            sheet.write(0, 2, '应用评分')
            sheet.write(0, 3, '下载次数')
            sheet.write(0, 4, '软件版本')
            sheet.write(0, 5, '更新时间')
            sheet.write(0, 6, '应用信息')
            sheet.write(0, 7, '用户评论')

            sheet.write(row, col, str(value['classification']))
            sheet.write(row, col + 1, str(value['app_name']))
            sheet.write(row, col + 2, str(value['app_mark']))
            sheet.write(row, col + 3, str(value['app_download']))
            sheet.write(row, col + 4, str(value['app_version']))
            sheet.write(row, col + 5, str(value['app_update']))  # 像表格中写入数据（对应的行和列）
            sheet.write(row, col + 6, str(value['app_info']))
            sheet.write(row, col + 7, str(value['app_coment']))
            workbook.save(path)  # 保存工作簿
            print("腾讯应用宝写入数据成功！")

    def write_excel_xls_append(self,path, value):
        index = len(value)  # 获取需要写入数据的行数
        workbook = xlrd.open_workbook(path)  # 打开工作簿
        sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
        worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
        rows_old = worksheet.nrows  # 获取表格中已存在的数据的行数
        new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
        new_worksheet = new_workbook.get_sheet(0)  # 获取转化后工作簿中的第一个表格
        new_worksheet.write(rows_old, 0, str(value['classification']))
        new_worksheet.write(rows_old, 1, str(value['app_name']))
        new_worksheet.write(rows_old, 2, str(value['app_mark']))
        new_worksheet.write(rows_old, 3, str(value['app_download']))
        new_worksheet.write(rows_old, 4, str(value['app_version']))
        new_worksheet.write(rows_old, 5, str(value['app_update']))  # 追加写入数据，注意是从i+rows_old行开始写入
        new_worksheet.write(rows_old, 6, str(value['app_info']))  # 追加写入数据，注意是从i+rows_old行开始写入
        new_worksheet.write(rows_old, 7, str(value['app_coment']))  # 追加写入数据，注意是从i+rows_old行开始写入
        new_workbook.save(path)  # 保存工作簿
        print("腾讯应用宝【追加】写入数据成功！")


    # # 生成excel文件
    # def generate_excel(self,expenses):
    #     # print(type(expenses))
    #     if os.path.exists('./rec_data.xlsx'):
    #        self.write_excel_xls_append('./rec_data.xlsx',expenses)
    #     else:
    #         workbook = xlsxwriter.Workbook('./rec_data.xlsx')
    #         worksheet = workbook.add_worksheet()
    #
    #         # 设定格式，等号左边格式名称自定义，字典中格式为指定选项
    #         # bold：加粗，num_format:数字格式
    #         bold_format = workbook.add_format({'bold': True})
    #         # money_format = workbook.add_format({'num_format': '$#,##0'})
    #         # date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
    #
    #         # 将二行二列设置宽度为15(从0开始)
    #         worksheet.set_column(1, 1, 15)
    #         # print(row)
    #         # 用符号标记位置，例如：A列1行
    #         worksheet.write('A1', 'sku_id', bold_format)
    #         worksheet.write('B1', 'sku_title', bold_format)
    #         worksheet.write('C1', 'id_1', bold_format)
    #         worksheet.write('D1', 'id_1_doc', bold_format)
    #         worksheet.write('E1', 'id_2_doc', bold_format)
    #         worksheet.write('F1', 'id_2_doc', bold_format)
    #         col = 0
    #         row = 1
    #         # for row in range(len(expenses)):
    #                 # 使用write_string方法，指定数据格式写入数据
    #         try:
    #             worksheet.write(row, col, str(expenses['app_name']))
    #             worksheet.write(row, col + 1, str(expenses['app_url']))
    #             worksheet.write(row, col + 2, str(expenses['app_download']))
    #             worksheet.write(row, col + 3, str(expenses['app_mark']))
    #             worksheet.write(row, col + 4, str(expenses['app_version']))
    #             worksheet.write(row, col + 5, str(expenses['app_update']))
    #         except:
    #             pass
    #         workbook.close()

    # def write_excel_xls_append(self,path, value):
    #     # index = len(value)  # 获取需要写入数据的行数
    #     print(path)
    #     workbook = xlrd.open_workbook(path)  # 打开工作簿
    #     sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    #     print(value)
    #     worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    #     rows_old = worksheet.nrows  # 获取表格中已存在的数据的行数
    #     print(rows_old)
    #     new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
    #     new_worksheet = new_workbook.get_sheet(0)  # 获取转化后工作簿中的第一个表格
    #     # for i in range(0, index):
    #     #     for j in range(0, len(value[i])):
    #     #         new_worksheet.write(i + rows_old, j, value[i][j])  # 追加写入数据，注意是从i+rows_old行开始写入
    #     new_worksheet.write(rows_old + 1, 0, str(value['app_name']))
    #     new_worksheet.write(rows_old + 1, 1, str(value['app_url']))
    #     new_worksheet.write(rows_old + 1, 2, str(value['app_download']))
    #     new_worksheet.write(rows_old + 1, 3, str(value['app_mark']))
    #     new_worksheet.write(rows_old + 1, 4, str(value['app_version']))
    #     new_worksheet.write(rows_old + 1, 5, str(value['app_update']))
    #
    #     new_workbook.save(path)  # 保存工作簿
    #     print("xls格式表格【追加】写入数据成功！")